<?php 
// +----------------------------------------------------------------------
// | RXThinkCMF敏捷开发框架 [ 赋能开发者，助力企业发展 ]
// +----------------------------------------------------------------------
// | 版权所有 2018~2023 南京RXThinkCMF研发中心
// +----------------------------------------------------------------------
// | Licensed LGPL-3.0 RXThinkCMF并不是自由软件，未经许可禁止去掉相关版权
// +----------------------------------------------------------------------
// | 官方网站: https://www.rxthink.cn
// +----------------------------------------------------------------------
// | Author: @牧羊人 团队荣誉出品
// +----------------------------------------------------------------------
// | 版权和免责声明:
// | 本团队对该软件框架产品拥有知识产权（包括但不限于商标权、专利权、著作权、商业秘密等）
// | 均受到相关法律法规的保护，任何个人、组织和单位不得在未经本团队书面授权的情况下对所授权
// | 软件框架产品本身申请相关的知识产权，禁止用于任何违法、侵害他人合法权益等恶意的行为，禁
// | 止用于任何违反我国法律法规的一切项目研发，任何个人、组织和单位用于项目研发而产生的任何
// | 意外、疏忽、合约毁坏、诽谤、版权或知识产权侵犯及其造成的损失 (包括但不限于直接、间接、
// | 附带或衍生的损失等)，本团队不承担任何法律责任，本软件框架禁止任何单位和个人、组织用于
// | 任何违法、侵害他人合法利益等恶意的行为，如有发现违规、违法的犯罪行为，本团队将无条件配
// | 合公安机关调查取证同时保留一切以法律手段起诉的权利，本软件框架只能用于公司和个人内部的
// | 法律所允许的合法合规的软件产品研发，详细声明内容请阅读《框架免责声明》附件；
// +----------------------------------------------------------------------

namespace app\admin\controller;


use app\admin\service\HistoryDataService;
use think\Db;

/**
 * 历史数据管理-控制器
 * @author 牧羊人
 * @since: 2023/10/03
 * Class Historydata
 * @package app\admin\controller
 */
class Historydata extends Backend
{
    /**
     * 初始化方法
     * @author 牧羊人
     * @since: 2023/10/03
     */
    public function initialize()
    {
        parent::initialize(); // TODO: Change the autogenerated stub
        $this->service = new HistoryDataService();
    }

    /**
     * 导出Excel
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @since 2021/3/25
     * @author 牧羊人
     */
    public function exportExcel()
    {
        $param = request()->param();

        $list = [];

        // 5分钟数据
        $dataType = $param['search_type'];

        // 关联设备
        $deviceNo = $param['device_no'];

        $list = [];

        if(isset($param['datetime']) && !empty($param['datetime'])){
            $sql = "SELECT FROM_UNIXTIME(create_time - MOD(create_time, ?),'%Y-%m-%d %H:%i:%s') AS time_block, 
                ROUND(AVG(bkll), 2) AS avg_bkll,
                ROUND(AVG(blLow), 2) AS avg_bllow,
                ROUND(AVG(wd), 2) AS avg_wd,
                ROUND(AVG(yl), 2) AS avg_yl,
                ROUND(AVG(yali), 2) AS avg_yali,
                ROUND(AVG(tao_value), 2) AS avg_tao_value, 
                ROUND(AVG(temp), 2) AS avg_temp, 
                ROUND(AVG(voltage), 2) AS avg_voltage,
                ROUND(AVG(chanshui), 2) AS avg_chanshui,
                ROUND(AVG(run_speed), 2) AS avg_run_speed,
                ROUND(AVG(pinlv), 2) AS avg_pinlv,
                ROUND(AVG(dianliu), 2) AS avg_dianliu,
                ROUND(AVG(biao_liuya), 2) AS avg_biao_liuya,
                ROUND(AVG(yezhu_high), 2) AS avg_yezhu_high,
                device_no
                FROM think_history_data
                WHERE create_time >= ? and create_time <= ? and device_no = ?
                GROUP BY time_block";
            $list = Db::query($sql,[$dataType,$param['datetime'][0] * 0.001,$param['datetime'][1] * 0.001,$deviceNo]);
        }else{
            // 快速搜索
            $hours = $param['fast_search'];
            $sql = "SELECT FROM_UNIXTIME(create_time - MOD(create_time, ?),'%Y-%m-%d %H:%i:%s') AS time_block, 
                ROUND(AVG(bkll), 2) AS avg_bkll,
                ROUND(AVG(blLow), 2) AS avg_bllow,
                ROUND(AVG(wd), 2) AS avg_wd,
                ROUND(AVG(yl), 2) AS avg_yl,
                ROUND(AVG(yali), 2) AS avg_yali,
                ROUND(AVG(tao_value), 2) AS avg_tao_value, 
                ROUND(AVG(temp), 2) AS avg_temp, 
                ROUND(AVG(voltage), 2) AS avg_voltage,
                ROUND(AVG(chanshui), 2) AS avg_chanshui,
                ROUND(AVG(run_speed), 2) AS avg_run_speed,
                ROUND(AVG(pinlv), 2) AS avg_pinlv,
                ROUND(AVG(dianliu), 2) AS avg_dianliu,
                ROUND(AVG(biao_liuya), 2) AS avg_biao_liuya,
                ROUND(AVG(yezhu_high), 2) AS avg_yezhu_high,
                device_no
                FROM think_history_data
                WHERE create_time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? HOUR)) and device_no = ?
                GROUP BY time_block";
            $list = Db::query($sql,[$dataType,$hours,$deviceNo]);
        }


        // 实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        // 激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '设备编号')
            ->setCellValue('B1', '流压(MPa)')
            ->setCellValue('C1', '校准流压(MPa)')
            ->setCellValue('D1', '井底温度(℃)')
            ->setCellValue('E1', '套压(MPa)')
            ->setCellValue('F1', '液柱高度(m)')
            ->setCellValue('G1', '瞬时气量(m³/h)')
            ->setCellValue('H1', '累计产气量(m³/h)')
            ->setCellValue('I1', '流量计温度(℃)')
            ->setCellValue('J1', '流量计压力(kPa)')
            ->setCellValue('K1', '频率(Hz)')
            ->setCellValue('L1', '电流(A)')
            ->setCellValue('M1', '电压(V)')
            ->setCellValue('N1', '产水(m³/h)')
            ->setCellValue('O1', '采集时间');

        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('K1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('L1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('M1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('N1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('O1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('L')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('M')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('N')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('O')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        // 设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(15);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('K')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('L')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('M')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('N')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('O')->setWidth(20);

        // 循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($list); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $list[$i]['device_no']);// 设备编号
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $list[$i]['avg_yali']);// 流压(MPa)
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $list[$i]['avg_biao_liuya']);// 校准流压(MPa)
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $list[$i]['avg_temp']);// 井底温度(℃)
            $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $list[$i]['avg_tao_value']);// 套压(MPa)
            $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $list[$i]['avg_yezhu_high']);// 液柱高度(m)'
            $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $list[$i]['avg_bkll']);// 瞬时气量(m³/h)
            $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), $list[$i]['avg_bllow']);// 累计产气量(m³/h)
            $objPHPExcel->getActiveSheet()->setCellValue('I' . ($i + 2), $list[$i]['avg_wd']);// 流量计温度(℃)
            $objPHPExcel->getActiveSheet()->setCellValue('J' . ($i + 2), $list[$i]['avg_yl']);// 流量计压力(kPa)
            $objPHPExcel->getActiveSheet()->setCellValue('K' . ($i + 2), $list[$i]['avg_pinlv']);// 频率(Hz)
            $objPHPExcel->getActiveSheet()->setCellValue('L' . ($i + 2), $list[$i]['avg_dianliu']);// 电流(A)
            $objPHPExcel->getActiveSheet()->setCellValue('M' . ($i + 2), $list[$i]['avg_voltage']);// 电压(V)
            $objPHPExcel->getActiveSheet()->setCellValue('N' . ($i + 2), $list[$i]['avg_chanshui']);// 产水(m³/h)
            $objPHPExcel->getActiveSheet()->setCellValue('O' . ($i + 2), $list[$i]['time_block']);// 运动时间
        }
        // 设置保存的Excel表格名称
        $filename = 'history_' . date('YmdHis', time()) . ".xlsx";
        // 设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('历史数据');

        // 保存本地文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        try {
            // 保存文件
            $objWriter->save(UPLOAD_TEMP_PATH . "/" . $filename);
        } catch (Exception $e) {
            echo "文件保存失败：" . $e->getMessage();
        }

        // 文件地址
        $filePath = get_image_url(str_replace(ATTACHMENT_PATH, "", UPLOAD_TEMP_PATH . "/" . $filename));
        return $this->jsonReturn(message("操作成功", true, $filePath));
    }
	
                	                                                                                    
}